#!/usr/bin/python
#coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR,INTEGER,DECIMAL
import pandas as pd
import datetime
from sqlalchemy.pool import NullPool
from common import db_utils as util
import sys
from common import df_operator as op

def getConnectionDb2(host, port, dbname, user, password):
    # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
    engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                           encoding="utf-8", poolclass=NullPool)
    return engine.connect()

def save_as_table(df,table_name,conn,schema_name=None,dtypedict=None):
    return pd.io.sql.to_sql(df, table_name, conn,schema_name,if_exists='append',index=False,chunksize=10000,dtype=dtypedict)

def INXDAY(DATE):
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_INDDAY WHERE DATE='""" + DATE + """ ' """;
    conn_rds.execute(del_sql)

    select_sql = """
        SELECT
            ACCOUNT                                                        AS ACCT,
            FACTORY                                                          AS DEPARTMENT_CODE,
            WORK_DATE                                                     AS DATE,
            UNIT                                                   AS UNIT_CODE,
            EDITION                                          AS IND,
            EDITION_NAME                                                         AS IND_NAME,
            EDITION_UNIT                                                          AS IND_UNIT,
            EDITION_VALUE                                                     AS ACT_VALUE,
            REMARK,
            REC_REVISOR,
            REC_REVISOR_TIME
        FROM
            M1_L1.SU_AJBG_AS0103 WHERE UNIT like 'Q%' AND WORK_DATE="""+DATE+"""
    """

    df = util.query(conn_mpp, select_sql)
    dtypedict = op.get_dict_type(df)
    util.insert(df, conn_rds, "T_ADS_FACT_SICB_INDDAY", "BGTARAS1", dtypedict)

def SACL01(DATE):
    del_sql = """delete from BGTARAS1.T_ADS_FACT_SICB_SACL01 WHERE DATE='"""+DATE+"""' """;
    conn_rds.execute(del_sql)

    select_sql = """
        SELECT 
            COST_CENTER,
            UNIT AS UNIT_CODE,
            DATE,
            WCE_TYPE AS COST_SUBJECT_TYPE,
            VAL AS ACT_VALUE,
            ST_NO AS STEELNO,
            THICK,
            THICK_COD AS THICK_CODE,
            WIDTH,
            WIDTH_COD AS WIDTH_CODE,
            IN_MAT_DIV,
            IN_MAT_DIV_CODE,
            PLAN_NO_C,
            PLAN_NO_COD AS PLAN_NO_CODE, 
            DESIGN_ANNEAL_DIAGRAM_CODE AS ANNEAL_CURVE,
            LAYER_TYPE AS COATING_TYPE,
            SPECE_TYPE AS SPEC_TYPE,
            IN_WIDTH AS ENTRY_WIDTH,
            IN_WIDTH_CODE AS ENTRY_WIDTH_CODE,
            QS_CODE AS PROD_CAT_CODE,
            QS_NAME AS PROD_CAT,
            TRIM_WIDTH_C AS TRIMMING_WIDTH_RANGE,
            TRIM_WIDTH_COD AS TRIMMING_WIDTH_CODE,
            LAS_NOTCH_FLAG AS PRODUCE_NICK_FLAG,
            '' AS REC_CREATOR,
            '' AS REC_CREATE_TIME,
            REC_REVISOR,
            REC_REVISOR_TIME
            FROM M1_L1.SU_AJBG_SACL01 WHERE UNIT like 'Q%' AND DATE="""+DATE+"""
    """

    df = util.query(conn_mpp, select_sql)
    dtypedict = op.get_dict_type(df)
    # print(dtypedict)
    util.insert(df, conn_rds, "T_ADS_FACT_SICB_SACL01", "BGTARAS1", dtypedict)

def SACL(date):
    global conn_mpp, conn_rds
    conn_mpp = util.getConnectionDb2('10.70.49.21', '50021 ', 'BGBDPROD', 'odsuser', 'odsuser')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    SACL01(date)
    INXDAY(date)
    conn_rds.close()
    conn_mpp.close()



if __name__ == '__main__':
    global conn_mpp, conn_rds
    conn_mpp = util.getConnectionDb2('10.70.49.21', '50021 ', 'BGBDPROD', 'odsuser', 'odsuser')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    main("20210716")
    main("20210715")
    main("20210714")
    conn_rds.close()
    conn_mpp.close()

